﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Text;

namespace Shc.Sql
{
    #region Sql Helper
    /// <summary>
    /// Sql makeup helper
    /// </summary>
    [Serializable]
    public class Sql
    {
        #region Criterion Expression
        /// <summary>
        /// =
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static EqExp Eq(string proExp, string valExp)
        {
            return new EqExp(proExp, valExp);
        }

        /// <summary>
        /// &lt;&gt;
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static NotEqExp NotEq(string proExp, string valExp)
        {
            return new NotEqExp(proExp, valExp);
        }

        /// <summary>
        /// &gt;=
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static GeExp Ge(string proExp, string valExp)
        {
            return new GeExp(proExp, valExp);
        }

        /// <summary>
        /// &gt;
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static GtExp Gt(string proExp, string valExp)
        {
            return new GtExp(proExp, valExp);
        }

        /// <summary>
        /// is null
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static IsNullExp IsNull(string proExp)
        {
            return new IsNullExp(proExp);
        }

        /// <summary>
        /// is not null
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static IsNotNullExp IsNotNull(string proExp)
        {
            return new IsNotNullExp(proExp);
        }

        /// <summary>
        /// &lt;=
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static LeExp Le(string proExp, string valExp)
        {
            return new LeExp(proExp, valExp);
        }

        /// <summary>
        /// &lt;
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static LtExp Lt(string proExp, string valExp)
        {
            return new LtExp(proExp, valExp);
        }

        /// <summary>
        /// between and
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="minExp"></param>
        /// <param name="maxExp"></param>
        /// <returns></returns>
        public static BetweenExp Between(string proExp, string minExp, string maxExp)
        {
            return new BetweenExp(proExp, minExp, maxExp);
        }

        /// <summary>
        /// not between and
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="minExp"></param>
        /// <param name="maxExp"></param>
        /// <returns></returns>
        public static NotBetweenExp NotBetween(string proExp, string minExp, string maxExp)
        {
            return new NotBetweenExp(proExp, minExp, maxExp);
        }

        /// <summary>
        /// like
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static LikeExp Like(string proExp, string valExp)
        {
            return new LikeExp(proExp, valExp);
        }

        /// <summary>
        /// not like
        /// </summary>
        /// <param name="proExp"></param>
        /// <param name="valExp"></param>
        /// <returns></returns>
        public static NotLikeExp NotLike(string proExp, string valExp)
        {
            return new NotLikeExp(proExp, valExp);
        }

        /// <summary>
        /// and
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public static AndExp And(Sql left, Sql right)
        {
            return new AndExp(left, right);
        }

        /// <summary>
        /// or
        /// </summary>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        public static OrExp Or(Sql left, Sql right)
        {
            return new OrExp(left, right);
        }

        public static Or2Exp Or(Sql[] left, Sql[] right)
        {
            return new Or2Exp(left, right);
        }

        /// <summary>
        /// in
        /// </summary>
        /// <param name="dml"></param>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static InDMLExp In(DMLExp dml, params Sql[] proExp)
        {
            return new InDMLExp(proExp, dml);
        }

        /// <summary>
        /// in
        /// </summary>
        /// <param name="pro"></param>
        /// <param name="range"></param>
        /// <returns></returns>
        public static InRangeExp InRange(Sql pro, params Sql[] range)
        {
            return new InRangeExp(pro, range);
        }


         /// <summary>
        /// not in
        /// </summary>
        /// <param name="dml"></param>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static NotInDMLExp NotIn(DMLExp dml, params Sql[] proExp)
        {
            return new NotInDMLExp(proExp, dml);
        }

        /// <summary>
        /// not in
        /// </summary>
        /// <param name="pro"></param>
        /// <param name="range"></param>
        /// <returns></returns>
        public static NotInRangeExp NotInRange(Sql pro, params Sql[] range)
        {
            return new NotInRangeExp(pro, range);
        }

        #endregion

        #region Projection Expression
        /// <summary>
        /// count
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static CountExp Count(string proExp)
        {
            return new CountExp(proExp);
        }

        /// <summary>
        /// sum
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static SumExp Sum(string proExp)
        {
            return new SumExp(proExp);
        }

        /// <summary>
        /// avg
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static AvgExp Avg(string proExp)
        {
            return new AvgExp(proExp);
        }

        /// <summary>
        /// min
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static MinExp Min(string proExp)
        {
            return new MinExp(proExp);
        }

        /// <summary>
        /// max
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static MaxExp Max(string proExp)
        {
            return new MaxExp(proExp);
        }
        #endregion

        #region Order Expression
        /// <summary>
        /// asc
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static AscExp Asc(string proExp)
        {
            return new AscExp(proExp);
        }

        /// <summary>
        /// desc
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static DescExp Desc(string proExp)
        {
            return new DescExp(proExp);
        }
        #endregion

        #region Sql DML
        /// <summary>
        /// Create dml
        /// </summary>
        /// <returns></returns>
        public static DMLExp DML()
        {
            return new DMLExp();
        }
        #endregion

        public static implicit operator Sql(string sql)
        {
            return new CommonExp(sql);
        }

        public static implicit operator string(Sql exp)
        {
            return exp.ToString();
        }
    }

    [Serializable]
    public class CommonExp
        : Sql
    {
        string m_exp;

        public string Exp
        {
            get { return m_exp; }
            set { m_exp = value; }
        }

        public CommonExp(string sqlExp)
        {
            m_exp = sqlExp;
        }

        public override string ToString()
        {
            return m_exp;
        }
    }

    #region Criterion Expression
    [Serializable]
    public class BetweenExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }
        string m_minExp;

        public string MinExp
        {
            get { return m_minExp; }
            set { m_minExp = value; }
        }
        string m_maxExp;

        public string MaxExp
        {
            get { return m_maxExp; }
            set { m_maxExp = value; }
        }

        public BetweenExp(string proExp, string minExp, string maxExp)
        {
            m_proExp = proExp;
            m_minExp = minExp;
            m_maxExp = maxExp;
        }

        public override string ToString()
        {
            return m_proExp + " between " + m_minExp + " and " + m_maxExp;
        }
    }

    [Serializable]
    public class NotBetweenExp
      : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }
        string m_minExp;

        public string MinExp
        {
            get { return m_minExp; }
            set { m_minExp = value; }
        }
        string m_maxExp;

        public string MaxExp
        {
            get { return m_maxExp; }
            set { m_maxExp = value; }
        }

        public NotBetweenExp(string proExp, string minExp, string maxExp)
        {
            m_proExp = proExp;
            m_minExp = minExp;
            m_maxExp = maxExp;
        }

        public override string ToString()
        {
            return m_proExp + " not between " + m_minExp + " and " + m_maxExp;
        }
    }

    [Serializable]
    public class EqExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public EqExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " = " + m_valExp;
        }
    }

    [Serializable]
    public class NotEqExp
        : Sql
    {
         string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public NotEqExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " <> " + m_valExp;
        }

    }

    [Serializable]
    public class GeExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public GeExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " >= " + m_valExp;
        }
    }

    [Serializable]
    public class GtExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public GtExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " > " + m_valExp;
        }
    }

    [Serializable]
    public class IsNullExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public IsNullExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return m_proExp + " is null";
        }
    }

    [Serializable]
    public class IsNotNullExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public IsNotNullExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return m_proExp + " is not null";
        }
    }

    [Serializable]
    public class LeExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public LeExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return ProExp + " <= " + ValExp;
        }
    }

    [Serializable]
    public class LtExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public LtExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " < " + m_valExp;
        }
    }

    [Serializable]
    public class LikeExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public LikeExp (string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " like " + m_valExp;
        }
    }

    [Serializable]
    public class NotLikeExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        string m_valExp;

        public string ValExp
        {
            get { return m_valExp; }
            set { m_valExp = value; }
        }

        public NotLikeExp(string proExp, string valExp)
        {
            m_proExp = proExp;
            m_valExp = valExp;
        }

        public override string ToString()
        {
            return m_proExp + " not like " + m_valExp;
        }
    }

    [Serializable]
    public class OrExp
        : Sql
    {
       Sql m_leftExp;

        public Sql LeftExp
        {
            get { return m_leftExp; }
            set { m_leftExp = value; }
        }

        Sql m_rightExp;

        public Sql RightExp
        {
            get { return m_rightExp; }
            set { m_rightExp = value; }
        }

        public OrExp(Sql left, Sql right)
        {
            m_leftExp = left;
            m_rightExp = right;
        }

        public override string ToString()
        {
            return "(" + m_leftExp + " or " + m_rightExp + ")";
        }
    }

    [Serializable]
    public class Or2Exp
        : Sql
    {
        Sql[] m_left;

        public Sql[] Left
        {
            get { return m_left; }
            set { m_left = value; }
        }

        Sql[] m_rigth;

        public Sql[] Rigth
        {
            get { return m_rigth; }
            set { m_rigth = value; }
        }

        public Or2Exp(Sql[] left, Sql[] right)
        {
            if (null == left || left.Length < 1 || null == right || right.Length < 1)
                throw new Exception("Or2Exp(Sql[] left, Sql[] right), left or right is null.");
            m_left = left;
            m_rigth = right;
        }

        public override string ToString()
        {
            string AndOp = " and ";

            string l = "";
            foreach(Sql s in Left)
                l += AndOp + s;
            l = "(" + l.Substring(AndOp.Length) + ")";

            string r = "";
            foreach(Sql s in Rigth)
                r += AndOp + s;
            r = "(" + r.Substring(AndOp.Length) + ")";

            return "(" + l + " or " + r  +")";
        }
    }

    [Serializable]
    public class AndExp
        : Sql
    {
        Sql m_leftExp;

        public Sql LeftExp
        {
            get { return m_leftExp; }
            set { m_leftExp = value; }
        }

        Sql m_rightExp;

        public Sql RightExp
        {
            get { return m_rightExp; }
            set { m_rightExp = value; }
        }

        public AndExp(Sql left, Sql right)
        {
            m_leftExp = left;
            m_rightExp = right;
        }

        public override string ToString()
        {
            return "(" + m_leftExp + " and " + m_rightExp + ")";
        }
    }

    [Serializable]
    public class InDMLExp
        : Sql
    {
        Sql[] m_proExps;
        DMLExp m_dml;

        public InDMLExp(Sql[] proExps, DMLExp dml)
        {
            m_proExps = proExps;
            m_dml = dml;
        }

        public override string ToString()
        {
            string sql = "";
            foreach (string p in m_proExps)
                sql += (string.IsNullOrEmpty(sql) ? "" : ",") + p;
            sql += string.IsNullOrEmpty(sql) ? "" : (" in (" + m_dml + ")");
            return sql;
        }

    }

    [Serializable]
    public class NotInDMLExp
        : Sql
    {
        Sql[] m_proExps;
        DMLExp m_dml;

        public NotInDMLExp(Sql[] proExps, DMLExp dml)
        {
            m_proExps = proExps;
            m_dml = dml;
        }

        public override string ToString()
        {
            string sql = "";
            foreach (string p in m_proExps)
                sql += (string.IsNullOrEmpty(sql) ? "" : ",") + p;
            sql += string.IsNullOrEmpty(sql) ? "" : (" not in (" + m_dml + ")");
            return sql;
        }

    }

    [Serializable]
    public class InRangeExp
        : Sql
    {
        Sql m_proExp;
        Sql[] m_rangeExp;
        
        public InRangeExp(Sql proExp, Sql[] rangeExp)
        {
            m_proExp = proExp;
            m_rangeExp = rangeExp;
        }

        public override string ToString()
        {
            string sql = "";
            foreach (string r in m_rangeExp)
                sql += (string.IsNullOrEmpty(sql) ? "" : ",") + r;
            sql = m_proExp + " in (" + sql + ")";
            return sql;
        }
    }

    [Serializable]
    public class NotInRangeExp
        : Sql
    {
        Sql m_proExp;
        Sql[] m_rangeExp;

        public NotInRangeExp(Sql proExp, Sql[] rangeExp)
        {
            m_proExp = proExp;
            m_rangeExp = rangeExp;
        }

        public override string ToString()
        {
            string sql = "";
            foreach (string r in m_rangeExp)
                sql += (string.IsNullOrEmpty(sql) ? "" : ",") + r;
            sql = m_proExp + " not in (" + sql + ")";
            return sql;
        }
    }
    #endregion

    #region Projection Expression
    [Serializable]
    public class CountExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public CountExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return "count(" + m_proExp + ")";
        }
    }

    [Serializable]
    public class AvgExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public AvgExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return "Avg(" + m_proExp + ")";
        }
    }

    [Serializable]
    public class MinExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public MinExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return "Min(" + m_proExp + ")";
        }
    }

    [Serializable]
    public class MaxExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public MaxExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return "Max(" + m_proExp + ")";
        }
    }

    [Serializable]
    public class SumExp
        : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        public SumExp(string proExp)
        {
            m_proExp = proExp;
        }

        public override string ToString()
        {
            return "Sum(" + m_proExp + ")";
        }
    }
    #endregion

    #region Order Expression
    [Serializable]
    public class Order
      : Sql
    {
        string m_proExp;

        public string ProExp
        {
            get { return m_proExp; }
            set { m_proExp = value; }
        }

        private Order()
        {

        }

        protected Order(string proExp)
        {
            m_proExp = proExp;
        }

        /// <summary>
        /// asc
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static AscExp Asc(string proExp)
        {
            return new AscExp(proExp);
        }

        /// <summary>
        /// desc
        /// </summary>
        /// <param name="proExp"></param>
        /// <returns></returns>
        public static DescExp Desc(string proExp)
        {
            return new DescExp(proExp);
        }
    }

    [Serializable]
    public class AscExp
        : Order
    {
        public AscExp(string proExp)
            : base(proExp)
        {
            
        }

        public override string ToString()
        {
            return base.ProExp + " asc";
        }
    }

    [Serializable]
    public class DescExp
        : Order
    {
        public DescExp(string proExp)
            : base(proExp)
        {
          
        }

        public override string ToString()
        {
            return base.ProExp + " desc";
        }
    }
    #endregion

    #region Sql DML
    [Serializable]
    public class DMLExp
        : Sql
    {
        public const string SELECT_TAG = " select ";
        public const string FROM_TAG = " from ";
        public const string WHERE_TAG = " where ";
        public const string GROUP_TAG = " group by ";
        public const string HAVING_TAG = " having ";
        public const string ORDER_TAG = " order by ";
        public const string INSERT_TAG = " insert into ";
        public const string VALUES_TAG = " values";
        public const string UPDATE_TAG = " update ";
        public const string SET_TAG = " set ";
        public const string DELETE_TAG = " delete from ";

        string m_sql = "";

        public DMLExp Append(string str)
        {
            m_sql += str;
            return this;
        }

        public override string ToString()
        {
            return m_sql;
        }

        #region Query
        string m_select = "";
        public string GetSelect() { return m_select; }
        string m_selectRestrict = "";
        public string GetSelectRestrict() { return m_selectRestrict; }
        string m_from = "";
        public string GetFrom() { return m_from; }
        string m_where = "";
        public string GetWhere() { return m_where; }
        string m_group = "";
        public string GetGroup() { return m_group; }
        string m_having = "";
        public string GetHaving() { return m_having; }
        string m_order = "";
        public string GetOrder() { return m_order; }

        public DMLExp Select(params Sql[] projections)
        {
            foreach (string p in projections)
            {
                if (string.IsNullOrEmpty(p))
                    continue;
                m_select += (string.IsNullOrEmpty(m_select) ? "" : ",") + p;
            }
            if (!string.IsNullOrEmpty(m_select))
                m_sql += SELECT_TAG +  m_select;
            return this;
        }

        public DMLExp SelectRestrict(Sql restrictExp, params Sql[] projections)
        {
            m_selectRestrict = restrictExp;
            foreach (string p in projections)
            {
                if (string.IsNullOrEmpty(p))
                    continue;
                m_select += (string.IsNullOrEmpty(m_select) ? "" : ",") + p;
            }
            if (!string.IsNullOrEmpty(m_select))
                m_sql += SELECT_TAG + restrictExp + " " + m_select;
            return this;
        }

        public DMLExp From(Sql fromExp)
        {
            m_from = fromExp;
            if (!string.IsNullOrEmpty(m_from))
                m_sql += FROM_TAG + m_from;
            return this;
        }

        public DMLExp Where(params Sql[] expressions)
        {
            foreach (string e in expressions)
            {
                if (string.IsNullOrEmpty(e))
                    continue;
                m_where += (string.IsNullOrEmpty(m_where) ? "" : " and ") + e;
            }
            if (!string.IsNullOrEmpty(m_where))
                m_sql += WHERE_TAG + m_where;
            return this;
        }

        public DMLExp Group(params Sql[] projections)
        {
            foreach (string p in projections)
            {
                if (string.IsNullOrEmpty(p))
                    continue;
                m_group += (string.IsNullOrEmpty(m_group) ? "" : ",") + p;
            }
            if (!string.IsNullOrEmpty(m_group))
                m_sql += GROUP_TAG + m_group;
            return this;
        }

        public DMLExp Having(params Sql[] expressions)
        {
            foreach (string e in expressions)
            {
                if (string.IsNullOrEmpty(e))
                    continue;
                m_having += (string.IsNullOrEmpty(m_having) ? "" : ",") + e;
            }
            if (!string.IsNullOrEmpty(m_having))
                m_sql += HAVING_TAG + m_having;
            return this;
        }

        public DMLExp Order(params Sql[] orders)
        {
            foreach (string o in orders)
            {
                if (string.IsNullOrEmpty(o))
                    continue;
                m_order += (string.IsNullOrEmpty(m_order) ? "" : ",") + o;
            }
            if (!string.IsNullOrEmpty(m_order))
                m_sql += ORDER_TAG + m_order;
            return this;
        }
        #endregion

        #region Insert
        string m_insert = "";
        string m_values = "";
       
        public DMLExp Insert(Sql table, params Sql[] projections)
        {
            foreach (string p in projections)
            {
                if (string.IsNullOrEmpty(p))
                    continue;
                m_insert += (string.IsNullOrEmpty(m_insert) ? "" : ",") + p;
            }
            if (!string.IsNullOrEmpty(m_insert))
                m_sql += INSERT_TAG + table + "(" + m_insert + ")";
            return this;
        }

        public DMLExp Values(params Sql[] values)
        {
            foreach (string v in values)
            {
                if (string.IsNullOrEmpty(v))
                    continue;
                m_values += (string.IsNullOrEmpty(m_values) ? "" : ",") + v;
            }
            if (!string.IsNullOrEmpty(m_values))
                m_sql += VALUES_TAG + "(" + m_values + ")";
            return this;
        }
        #endregion

        #region Update
        string m_update = "";
        string m_set = "";
        public DMLExp Update(Sql table)
        {
            m_update = table;
            if(!string.IsNullOrEmpty(m_update))
                m_sql += UPDATE_TAG + m_update;
            return this;
        }

        public DMLExp Set(params EqExp[] sets)
        {
            foreach (string s in sets)
            {
                if (string.IsNullOrEmpty(s))
                    continue;
                m_set += (string.IsNullOrEmpty(m_set) ? "" : ",") + s;
            }
            if (!string.IsNullOrEmpty(m_set))
                m_sql += SET_TAG + m_set;
            return this;
        }
        #endregion

        #region Delete
        string m_delete = "";
        public DMLExp Delete(Sql table)
        {
            m_delete = table;
            if (!string.IsNullOrEmpty(m_delete))
                m_sql += DELETE_TAG + m_delete;
            return this;
        }
        #endregion
    }
    #endregion

    #endregion

    #region DBObject Type Converter
    /// <summary>
    /// DB Object, It can convert to c# simple tpye
    /// </summary>
    public class DBObject
    {
        object m_obj;

        public object Obj
        {
            get { return m_obj; }
        }

        public DBObject(object obj)
        {
            m_obj = obj;
        }

        /// <summary>
        /// Convert to bool
        /// </summary>
        public static implicit operator bool?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (bool?)Convert.ToBoolean(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator bool(DBObject obj)
        {
            bool? val = obj;
            return val == null ? false : (bool)val;
        }

        /// <summary>
        /// Convert to byte
        /// </summary>
        public static implicit operator byte?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (byte?)Convert.ToByte(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator byte(DBObject obj)
        {
            byte? val = obj;
            return val == null ? (byte)0 : (byte)val;
        }

        /// <summary>
        /// Convert to char
        /// </summary>
        public static implicit operator char?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (char?)Convert.ToChar(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator char(DBObject obj)
        {
            char? val = obj;
            return val == null ? '\0' : (char)val;
        }

        /// <summary>
        /// Convert to DateTime
        /// </summary>
        public static implicit operator DateTime?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (DateTime?)Convert.ToDateTime(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator DateTime(DBObject obj)
        {
            DateTime? val = obj;
            return val == null ? DateTime.MinValue : (DateTime)val;
        }

        /// <summary>
        /// Convert to decimal
        /// </summary>
        public static implicit operator decimal?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (decimal?)Convert.ToDecimal(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator decimal(DBObject obj)
        {
            decimal? val = obj;
            return val == null ? (decimal)0 : (decimal)val;
        }

        /// <summary>
        /// Convert to double
        /// </summary>
        public static implicit operator double?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (double?)Convert.ToDouble(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator double(DBObject obj)
        {
            double? val = obj;
            return val == null ? (double)0 : (double)val;
        }

        /// <summary>
        /// Convert to short
        /// </summary>
        public static implicit operator short?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (short?)Convert.ToInt16(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator short(DBObject obj)
        {
            short? val = obj;
            return val == null ? (short)0 : (short)val;
        }

        /// <summary>
        /// Convert to int
        /// </summary>
        public static implicit operator int?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (int?)Convert.ToInt32(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator int(DBObject obj)
        {
            int? val = obj;
            return val == null ? 0 : (int)val;
        }

        /// <summary>
        /// Convert to long
        /// </summary>
        public static implicit operator long?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (long?)Convert.ToInt64(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator long(DBObject obj)
        {
            long? val = obj;
            return val == null ? 0 : (long)val;
        }

        /// <summary>
        /// Convert to sbyte
        /// </summary>
        public static implicit operator sbyte?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (sbyte?)Convert.ToSByte(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator sbyte(DBObject obj)
        {
            sbyte? val = obj;
            return val == null ? (sbyte)0 : (sbyte)val;
        }

        /// <summary>
        /// Convert to float
        /// </summary>
        public static implicit operator float?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (float?)Convert.ToSingle(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator float(DBObject obj)
        {
            float? val = obj;
            return val == null ? (float)0 : (float)val;
        }

        /// <summary>
        /// Convert to String
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static implicit operator string(DBObject obj)
        {
            try
            {
                if (obj == null) return null;
                return Convert.ToString(obj.m_obj);
            }
            catch { return null; }
        }

        /// <summary>
        /// Convert to ushort
        /// </summary>
        public static implicit operator ushort?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (ushort?)Convert.ToUInt16(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator ushort(DBObject obj)
        {
            ushort? val = obj;
            return val == null ? (ushort)0 : (ushort)val;
        }

        /// <summary>
        /// Convert to uint
        /// </summary>
        public static implicit operator uint?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (uint?)Convert.ToUInt32(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator uint(DBObject obj)
        {
            uint? val = obj;
            return val == null ? (uint)0 : (uint)val;
        }

        /// <summary>
        /// Convert to ulong
        /// </summary>
        public static implicit operator ulong?(DBObject obj)
        {
            try
            {
                return obj == null ? null : (ulong?)Convert.ToUInt64(obj.m_obj);
            }
            catch { return null; }
        }

        public static implicit operator ulong(DBObject obj)
        {
            ulong? val = obj;
            return val == null ? (ulong)0 : (ulong)val;
        }

        public static implicit operator byte[](DBObject obj)
        {
            if (obj == null || obj.m_obj == null || !(obj.m_obj is byte[]))
                return new byte[0];
            return (byte[])obj.m_obj;
        }
    }
    #endregion

    #region Entity
    /// <summary>
    /// Reader base on IDataReader
    /// </summary>
    public class RowDataReader
    {
        Dictionary<string, int> m_colDic;
        IDataReader m_reader;

        public RowDataReader(Dictionary<string, int> colDic, IDataReader reader)
        {
            m_colDic = colDic;
            m_reader = reader;
        }

        public DBObject this[string colName]
        {
            get
            {
                object obj = null;
                if (!m_colDic.ContainsKey(colName) || (obj = m_reader[m_colDic[colName]]) is DBNull)
                    return null;
                return new DBObject(obj);
            }
        }

        public DBObject this[int colIndex]
        {
            get
            {
                if (colIndex < 0 || colIndex >= m_reader.FieldCount)
                    return null;
                return new DBObject(m_reader[colIndex] is DBNull ? null : m_reader[colIndex]);
            }
        }
    }

    /// <summary>
    /// Primary Key Type
    /// </summary>
    public enum PrimaryKeyType
    {
        None,
        InCrement,
        Identity,
        Sequence,
        Hilo,
        Sqehilo,
        Uuid,
        Guid,
        Native,
        Assigned,
        Select,
        Foreign,
        SequenceIdentity,
    }



    public class PrimaryKey
    {
        string m_keyName;

        public string KeyName
        {
            get { return m_keyName; }
        }

        PrimaryKeyType m_PKType;

        public PrimaryKeyType PKType
        {
            get { return m_PKType; }
            set { m_PKType = value; }
        }

        object m_value;

        public object Value
        {
            get { return m_value; }
            set { m_value = value; }
        }

        public PrimaryKey(string keyName, PrimaryKeyType pkType, object value)
        {
            m_keyName = keyName;
            m_PKType = pkType;
            m_value = value;
        }
    }

    public class RowDataWriter
        : Dictionary<string, object>
    {
        List<PrimaryKey> m_primaryKeys = new List<PrimaryKey>();

        public List<PrimaryKey> PrimaryKeys
        {
            get { return m_primaryKeys; }
        }

        public void SetPrimaryKey(string keyName, PrimaryKeyType pkType, object value)
        {
            m_primaryKeys.Add(new PrimaryKey(keyName, pkType, value));
        }
    }

    /// <summary>
    /// Entity Interface
    /// </summary>
    public interface IDbSessionEntity
    {
        string TableName {get;}
        void ReadDBData(RowDataReader reader);
        void WriteDBData(RowDataWriter writer);
        void SetIdentityAfterInsert(DBObject idd);
    }

    #endregion

    #region Sql Session

    public static class DbSessionDebuger
    {
        public static string GetDebugInfo(Exception ex)
        {
            StringBuilder sb = new StringBuilder();
            System.IO.StringWriter sw = new System.IO.StringWriter(sb);
            sw.WriteLine(DateTime.Now.ToString() + " Exception:");
            int index = 1;
            do
            {
                sw.WriteLine("Message" + index.ToString() + ":" + ex.Message);
                if (!string.IsNullOrEmpty(ex.StackTrace))
                    sw.WriteLine("StackTrace" + index.ToString() + ":" + ex.StackTrace);
                index++;
            } while (null != (ex = ex.InnerException));
            return sb.ToString();
        }

        public static string GetDebugInfo(Exception ex, DMLExp sql, IDataParameter[] sqlParams)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(GetSqlExecuteInfo(sql, sqlParams));
            sb.AppendLine(GetDebugInfo(ex));
            return sb.ToString();
        }

        public static string GetSqlExecuteInfo(DMLExp sql, IDataParameter[] sqlParams)
        {
            StringBuilder sb = new StringBuilder();
            System.IO.StringWriter sw = new System.IO.StringWriter(sb);
            sw.WriteLine("Sql:" + sql);
            if (sqlParams == null)
                return sb.ToString();
            sw.Write("Params:");
            foreach (IDataParameter p in sqlParams)
                sw.Write((string.IsNullOrEmpty(p.ParameterName) ? "" : p.ParameterName) + "=" +
                    (p.Value == null ? "<null>" : p.Value.ToString()) + ";");
            sw.WriteLine("");
            return sb.ToString();
        }

        public static void Debug(Exception ex)
        {
            System.Console.WriteLine(GetDebugInfo(ex));
            throw ex;
        }

        public static void DebugSqlExecute(DMLExp sql, IDataParameter[] sqlParams)
        {
            System.Console.WriteLine(GetSqlExecuteInfo(sql, sqlParams));
        }
    }

    #region EventArgs
    public class DbSessionErrorEventArgs : EventArgs
    {
        Exception m_exception;

        public Exception Exception
        {
            get { return m_exception; }
        }

        string m_debugInfo;

        public string DebugInfo
        {
            get { return m_debugInfo; }
        }

        public DbSessionErrorEventArgs(string debugInfo, Exception exception)
        {
            m_debugInfo = debugInfo;
            m_exception = exception;
        }
    }

    public class DbSessionSqlEventArgs : EventArgs
    {
        DMLExp m_sqlExecute;

        public DMLExp SqlExecute
        {
            get { return m_sqlExecute; }
        }

        private IDataParameter[] m_sqlParams;

        public IDataParameter[] SqlParams
        {
            get { return m_sqlParams; }
            set { m_sqlParams = value; }
        }

        bool executeReturn = false;
        /// <summary>
        /// return when stop is true
        /// </summary>
        public bool ExecuteReturn
        {
            get { return executeReturn; }
            set { executeReturn = value; }
        }

        bool m_stop = false;
        /// <summary>
        /// Get, Stop dbSession execute.
        /// </summary>
        public bool Stop
        {
            get { return m_stop; }
        }

        public DbSessionSqlEventArgs(DMLExp sql, IDataParameter[] sqlParams)
        {
            m_sqlExecute = sql;
            m_sqlParams = sqlParams;
        }
    }

    #endregion

    public class DbSession : IDisposable
    {
        IDbConnection m_iConnection;

        public IDbConnection IConnection
        {
            get { return m_iConnection; }
        }

        private DbSession(IDbConnection iConnection)
        {
            m_iConnection = iConnection;
        }

        public static DbSession Create(IDbConnection iConnection)
        {
            return new DbSession(iConnection);
        }

        IDbTransaction m_levelTran = null;

        internal IDbTransaction LevelTran
        {
            get { return m_levelTran; }
            set { m_levelTran = value; }
        } 

        #region Events
        public event EventHandler<DbSessionErrorEventArgs> OnError;
        public event EventHandler OnBeforeOpen;
        public event EventHandler OnAfterOpen;
        public event EventHandler OnBeforeClose;
        public event EventHandler OnAfterClose;
        public event EventHandler<DbSessionSqlEventArgs> OnBeforeExecute;
        public event EventHandler<DbSessionSqlEventArgs> OnAfterExecute;
        public event EventHandler<DbSessionSqlEventArgs> OnBeforeGetCellData;
        public event EventHandler<DbSessionSqlEventArgs> OnAfterGetCellData;
        public event EventHandler<DbSessionSqlEventArgs> OnBeforeGetRowData;
        public event EventHandler<DbSessionSqlEventArgs> OnAfterGetRowData;
        public event EventHandler<DbSessionSqlEventArgs> OnBeforeGetListData;
        public event EventHandler<DbSessionSqlEventArgs> OnAfterGetListData;
        #endregion

        #region Open & Close Session
        /// <summary>
        /// Open DB
        /// </summary>
        /// <returns></returns>
        public bool OpenSession()
        {
            //before
            if (null != OnBeforeOpen)
                OnBeforeOpen(this, new EventArgs());
            try
            {
                m_iConnection.Open();

                //after
                if (null != OnAfterOpen)
                    OnAfterOpen(this, new EventArgs());

                return true;
            }
            catch (System.Exception ex)
            {
#if DEBUG
                DbSessionDebuger.Debug(ex);
#endif
                if (null != OnError)
                    OnError(this, new DbSessionErrorEventArgs(DbSessionDebuger.GetDebugInfo(ex), ex));
                return false;
            }
        }

        /// <summary>
        /// Close DB
        /// </summary>
        private void CloseSession()
        {
            //before
            if (null != OnBeforeClose)
                OnBeforeClose(this, new EventArgs());
            try
            {
                m_iConnection.Close();
                //after
                if (null != OnAfterClose)
                    OnAfterClose(this, new EventArgs());
            }
            catch (System.Exception ex)
            {

            }
        }
        #endregion

        #region DML Operation

        /// <summary>
        /// Execute sql, such as Insert, Update, Delete and so on.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public bool Execute(DMLExp sql, params IDataParameter[] paramList)
        {
            //before execute
#if DEBUG
            DbSessionDebuger.DebugSqlExecute(sql, paramList);
#endif
            if (null != OnBeforeExecute)
            {
                DbSessionSqlEventArgs args = new DbSessionSqlEventArgs(sql, paramList);
                OnBeforeExecute(this, args);
                if (args.Stop)
                    return args.ExecuteReturn;
            }

            try
            {  
                //execute
                using(IDbCommand cmd = m_iConnection.CreateCommand())
                {
                    if (LevelTran != null)
                        cmd.Transaction = LevelTran;
                    cmd.CommandText = sql;
                    foreach (IDataParameter p in paramList)
                        cmd.Parameters.Add(p);
                    cmd.ExecuteNonQuery();
                }

                //after execute
                if (null != OnAfterExecute)
                    OnAfterExecute(this, new DbSessionSqlEventArgs(sql, paramList));
                return true;
            }
            catch (System.Exception ex)
            {
#if DEBUG
                DbSessionDebuger.Debug(ex);
#endif
                if (null != OnError)
                    OnError(this, new DbSessionErrorEventArgs(DbSessionDebuger.GetDebugInfo(ex, sql, paramList), ex));
                return false;
            }
        }

        /// <summary>
        /// Get the first column of first row of the sql query result.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public DBObject GetCellData(DMLExp sql, params IDataParameter[] paramList)
        {
            //before
#if DEBUG
            DbSessionDebuger.DebugSqlExecute(sql, paramList);
#endif
            if (null != OnBeforeGetCellData)
            {
                DbSessionSqlEventArgs args = new DbSessionSqlEventArgs(sql, paramList);
                OnBeforeGetCellData(this, args);
                if (args.Stop)
                    return null;
            }

            try
            {
                object result = null;
                //execute
                using(IDbCommand cmd = m_iConnection.CreateCommand())
                {
                    if (LevelTran != null)
                        cmd.Transaction = LevelTran;
                    cmd.CommandText = sql;
                    foreach (IDataParameter p in paramList)
                        cmd.Parameters.Add(p);
                    result = cmd.ExecuteScalar();
                }
               
                //after
                if (null != OnAfterGetCellData)
                    OnAfterGetCellData(this, new DbSessionSqlEventArgs(sql, paramList));

                return new DBObject(result);
            }
            catch (System.Exception ex)
            {
#if DEBUG
                DbSessionDebuger.Debug(ex);
#endif
                if (null != OnError)
                    OnError(this, new DbSessionErrorEventArgs(DbSessionDebuger.GetDebugInfo(ex, sql, paramList), ex));
                return null;
            }
        }

        #region GetRowData & GetListData
        public delegate void FillReaderDataHandler(Dictionary<string, int> rowDic, IDataReader reader, object tobj);
        private void FillDictionary(Dictionary<string, int> rowDic, IDataReader reader, object tobj)
        {
            IDictionary dic = (IDictionary)tobj;
            for (int i = 0; i < reader.FieldCount; i++)
                dic[reader.GetName(i)] = reader.GetValue(i);
        }
        private void FillEntity(Dictionary<string, int> rowDic, IDataReader reader, object tobj)
        {
            IDbSessionEntity entity = (IDbSessionEntity)tobj;
            entity.ReadDBData(new RowDataReader(rowDic, reader));
        }

        /// <summary>
        /// Get the first row of sql query result.
        /// </summary>
        /// <param name="rowObj">row data content</param>
        /// <param name="funFillRow">function which fill rowObj</param>
        /// <param name="sql">sql</param>
        /// <param name="paramList">params</param>
        /// <returns></returns>
        public bool GetRowData(object rowObj, FillReaderDataHandler funFillRow, DMLExp sql, params IDataParameter[] paramList)
        {
            //before
#if DEBUG
            DbSessionDebuger.DebugSqlExecute(sql, paramList);
#endif
            if(null != OnBeforeGetRowData)
            {
                DbSessionSqlEventArgs args = new DbSessionSqlEventArgs(sql, paramList);
                OnBeforeGetRowData(this, args);
                if (args.Stop)
                    return false;
            }

            try
            {
                //execute
                using(IDbCommand cmd = m_iConnection.CreateCommand())
                {
                    if (LevelTran != null)
                        cmd.Transaction = LevelTran;
                    cmd.CommandText = sql;
                    foreach (IDataParameter p in paramList)
                        cmd.Parameters.Add(p);
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        Dictionary<string, int> rowDic = new Dictionary<string, int>();
                        for (int i = 0; i < reader.FieldCount; i++)
                            rowDic[reader.GetName(i)] = i;
                        if (reader.Read())
                            funFillRow(rowDic, reader, rowObj);
                        else
                            return false;
                    }
                }

                //after
                if (null != OnAfterGetRowData)
                    OnAfterGetRowData(this, new DbSessionSqlEventArgs(sql, paramList));

                return true;
            }
            catch (System.Exception ex)
            {
#if DEBUG
                DbSessionDebuger.Debug(ex);
#endif
                if (null != OnError)
                    OnError(this, new DbSessionErrorEventArgs(DbSessionDebuger.GetDebugInfo(ex, sql, paramList), ex));
                return false;
            }
        }

        /// <summary>
        /// Get the first row of sql query result to IDictionary
        /// </summary>
        /// <param name="rowDic">row IDictionary</param>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public bool GetRowData(IDictionary rowDic, DMLExp sql, params IDataParameter[] paramList)
        {
            return GetRowData(rowDic, new FillReaderDataHandler(FillDictionary), sql, paramList);
        }

        /// <summary>
        /// Get the first row of sql query result to IDbSessionEntity
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public bool GetRowData(IDbSessionEntity entity, DMLExp sql, params IDataParameter[] paramList)
        {
            return GetRowData(entity, new FillReaderDataHandler(FillEntity), sql, paramList);
        }

        /// <summary>
        /// Get sql query result to IList
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="listObj">list data content</param>
        /// <param name="funFillRow">function which fill rowObj</param>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public bool GetListData<T>(IList<T> listObj, FillReaderDataHandler funFillRow, DMLExp sql, params IDataParameter[] paramList)
            where T : new()
        {
            //before
#if DEBUG
            DbSessionDebuger.DebugSqlExecute(sql, paramList);
#endif
            if (null != OnBeforeGetListData)
            {
                DbSessionSqlEventArgs args = new DbSessionSqlEventArgs(sql, paramList);
                OnBeforeGetListData(this, args);
                if (args.Stop)
                    return false;
            }

            try
            {
                //execute
                using (IDbCommand cmd = m_iConnection.CreateCommand())
                {
                    if (LevelTran != null)
                        cmd.Transaction = LevelTran;
                    cmd.CommandText = sql;
                    foreach (IDataParameter p in paramList)
                        cmd.Parameters.Add(p);
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        Dictionary<string, int> rowDic = new Dictionary<string, int>();
                        for (int i = 0; i < reader.FieldCount; i++)
                            rowDic[reader.GetName(i)] = i;
                        while (reader.Read())
                        {
                            T rowObj = new T();
                            funFillRow(rowDic, reader, rowObj);
                            listObj.Add(rowObj);
                        }
                    }
                }

                //after
                if (null != OnAfterGetListData)
                    OnAfterGetListData(this, new DbSessionSqlEventArgs(sql, paramList));

                return true;
            }
            catch (System.Exception ex)
            {
#if DEBUG
                DbSessionDebuger.Debug(ex);
#endif
                if (null != OnError)
                    OnError(this, new DbSessionErrorEventArgs(DbSessionDebuger.GetDebugInfo(ex, sql, paramList), ex));
                return false;
            }
        }


        /// <summary>
        /// Get sql query result to IDbSessionEntity IList
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dicList"></param>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public bool GetListData<T>(IList<T> list, DMLExp sql, params IDataParameter[] paramList)
            where T : IDbSessionEntity, new()
        {
            return GetListData<T>(list, new FillReaderDataHandler(FillEntity), sql, paramList);
        }

        /// <summary>
        /// Get sql query result to Hashtable IList
        /// </summary>
        /// <param name="hashObj"></param>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public bool GetListData(IList<Hashtable> hashObj, DMLExp sql, params IDataParameter[] paramList)
        {
            return GetListData<Hashtable>(hashObj, new FillReaderDataHandler(FillDictionary), sql, paramList);
        }

        /// <summary>
        /// Get sql query result to DataTable
        /// </summary>
        /// <param name="adapter"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool GetListData(IDataAdapter adapter, DataSet ds)
        {
            //before
            IDataParameter[] paramList = adapter.GetFillParameters();
            DMLExp sql = Sql.DML();
#if DEBUG
            DbSessionDebuger.DebugSqlExecute(sql, paramList);
#endif
            if (null != OnBeforeGetListData)
            {
                DbSessionSqlEventArgs args = new DbSessionSqlEventArgs(sql, paramList);
                OnBeforeGetListData(this, args);
                if (args.Stop)
                    return false;
            }

            try
            {
                //execute
                adapter.Fill(ds);

                //after
                if (null != OnAfterGetListData)
                    OnAfterGetListData(this, new DbSessionSqlEventArgs(sql, paramList));

                return true;
            }
            catch (System.Exception ex)
            {
#if DEBUG
                DbSessionDebuger.Debug(ex);
#endif
                if (null != OnError)
                    OnError(this, new DbSessionErrorEventArgs(DbSessionDebuger.GetDebugInfo(ex, sql, paramList), ex));
                return false;
            }
        }

        #endregion

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            CloseSession();
        }

        #endregion
    }
    #endregion

    #region DbSession Trans Manager
    public class DbTransCounter : IDisposable
    {
        private int m_count = 0;
        private bool m_isAutoCloseSession = false;
        private DbSession m_ss;
        private IDbTransaction m_tran = null;

        public DbSession Session
        {
            get
            {
                if (null == m_tran)
                    throw new Exception("Please BeginTransaction First!!!");
                return m_ss;
            }
        }

        public DbTransCounter(DbSession ss, bool isAutoCloseSession)
        {
            m_ss = ss;
            m_isAutoCloseSession = isAutoCloseSession;
        }

        public DbTransCounter BeginTransaction()
        {
            if (m_count == 0)
            {
                m_tran = m_ss.IConnection.BeginTransaction();
                if (m_tran == null)
                    return this;
                Session.LevelTran = m_tran;
            }
            m_count++;
            return this;
        }

        public DbTransCounter Rollback()
        {
            if (m_count == 0)
                return this;
            m_tran.Rollback();
            m_count = 0;
            Session.LevelTran = null;
            return this;
        }

        public DbTransCounter Commit()
        {
            if (m_count == 1)
            {
                m_tran.Commit();
                Session.LevelTran = null;
            }
            if (m_count - 1 >= 0)
                m_count--;
            return this;
        }

        public void Dispose()
        {
            if (m_isAutoCloseSession)
                m_ss.Dispose();
        }
    }

    #endregion

    #region Criterion
    [Serializable]
    public class Criterion
    {
        public enum Type
        {
            Eq, NotEq, Ge, Gt, IsNull, IsNotNull, Le, Lt, Between,
            LikeNone, LikeBegin, LikeEnd, LikeMid,
            NotLikeNone, NotLikeBegin, NotLikeEnd, NotLikeMid,
            Or, Or2, In, InDQL, NotIn, NotInDQL
        }

        Type m_criterionType;

        public Type CriterionType
        {
            get { return m_criterionType; }
            set { m_criterionType = value; }
        }

        string m_pro;

        public string Pro
        {
            get { return m_pro; }
        }

        object[] m_vals;

        public object[] Vals
        {
            get { return m_vals; }
        }

        public Criterion(Type type, string pro, params object[] vals)
        {
            m_criterionType = type;
            m_pro = pro;
            m_vals = vals;
        }

        public static Criterion Eq(string pro, object val)
        {
            return new Criterion(Type.Eq, pro, val);
        }

        public static Criterion NotEq(string pro, object val)
        {
            return new Criterion(Type.NotEq, pro, val);
        }

        public static Criterion Ge(string pro, object val)
        {
            return new Criterion(Type.Ge, pro, val);
        }

        public static Criterion Gt(string pro, object val)
        {
            return new Criterion(Type.Gt, pro, val);
        }

        public static Criterion IsNull(string pro)
        {
            return new Criterion(Type.IsNull, pro);
        }

        public static Criterion IsNotNull(string pro)
        {
            return new Criterion(Type.IsNotNull, pro);
        }

        public static Criterion Le(string pro, object val)
        {
            return new Criterion(Type.Le, pro, val);
        }

        public static Criterion Lt(string pro, object val)
        {
            return new Criterion(Type.Lt, pro, val);
        }

        public static Criterion Between(string pro, object minVal, object maxVal)
        {
            return new Criterion(Type.Between, pro, minVal, maxVal);
        }

        public static Criterion LikeNone(string pro, object val)
        {
            return new Criterion(Type.LikeNone, pro, val);
        }

        public static Criterion LikeBegin(string pro, object val)
        {
            return new Criterion(Type.LikeBegin, pro, val);
        }

        public static Criterion LikeEnd(string pro, object val)
        {
            return new Criterion(Type.LikeEnd, pro, val);
        }

        public static Criterion LikeMid(string pro, object val)
        {
            return new Criterion(Type.LikeMid, pro, val);
        }

        public static Criterion NotLikeNone(string pro, object val)
        {
            return new Criterion(Type.NotLikeNone, pro, val);
        }

        public static Criterion NotLikeBegin(string pro, object val)
        {
            return new Criterion(Type.NotLikeBegin, pro, val);
        }

        public static Criterion NotLikeEnd(string pro, object val)
        {
            return new Criterion(Type.NotLikeEnd, pro, val);
        }

        public static Criterion NotLikeMid(string pro, object val)
        {
            return new Criterion(Type.NotLikeMid, pro, val);
        }

        public static Criterion Or(Criterion left, Criterion right)
        {
            return new Criterion(Type.Or, null, left, right);
        }

        public static Criterion Or2(Criterion[] left, Criterion[] right)
        {
            return new Criterion(Type.Or2, null, left, right);
        }

        public static Criterion In(string pro, params object[] range)
        {
            return new Criterion(Type.In, pro, range);
        }

        public static Criterion InDQL(string proExp, string dqlProExp, string dqlTable, Criterion[] dqlCri)
        {
            return new Criterion(Type.InDQL, proExp, new object[] { dqlProExp, dqlTable, dqlCri });
        }

        public static Criterion NotIn(string pro, params object[] range)
        {
            return new Criterion(Type.NotIn, pro, range);
        }

        public static Criterion NotInDQL(string proExp, string dqlProExp, string dqlTable, Criterion[] dqlCri)
        {
            return new Criterion(Type.NotInDQL, proExp, new object[] { dqlProExp, dqlTable, dqlCri });
        }

        protected static string GetUniqueParamName(IDBManagerHelper IDbMgr, ref int uniqueIdCount)
        {
            return IDbMgr.MakeParam("p" + (uniqueIdCount++).ToString());
        }

        public static void Fetch(IDBManagerHelper IDbMgr, Criterion[] cris, List<Sql> sqlList, List<IDbDataParameter> paramList, ref int uniqueIdCount)
        {
            string pname = "", pname2 = "";
            foreach (Criterion cri in cris)
            {
                pname = GetUniqueParamName(IDbMgr, ref uniqueIdCount);
                switch (cri.CriterionType)
                {
                    case Type.Eq:
                        sqlList.Add(Sql.Eq(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.NotEq:
                        sqlList.Add(Sql.NotEq(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.Ge:
                        sqlList.Add(Sql.Ge(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.Gt:
                        sqlList.Add(Sql.Gt(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.IsNull:
                        sqlList.Add(Sql.IsNull(cri.Pro));
                        break;
                    case Type.IsNotNull:
                        sqlList.Add(Sql.IsNotNull(cri.Pro));
                        break;
                    case Type.Le:
                        sqlList.Add(Sql.Le(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.Lt:
                        sqlList.Add(Sql.Lt(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.Between:
                        pname2 = GetUniqueParamName(IDbMgr, ref uniqueIdCount);
                        sqlList.Add(Sql.Between(cri.Pro, pname, pname2));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        paramList.Add(IDbMgr.SetParam(pname2, cri.Vals[1]));
                        break;
                    case Type.LikeNone:
                        sqlList.Add(Sql.Like(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.LikeBegin:
                        sqlList.Add(Sql.Like(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, (string)cri.Vals[0] + "%"));
                        break;
                    case Type.LikeEnd:
                        sqlList.Add(Sql.Like(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, "%" + (string)cri.Vals[0]));
                        break;
                    case Type.LikeMid:
                        sqlList.Add(Sql.Like(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, "%" + (string)cri.Vals[0] + "%"));
                        break;
                    case Type.NotLikeNone:
                        sqlList.Add(Sql.NotLike(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, cri.Vals[0]));
                        break;
                    case Type.NotLikeBegin:
                        sqlList.Add(Sql.NotLike(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, (string)cri.Vals[0] + "%"));
                        break;
                    case Type.NotLikeEnd:
                        sqlList.Add(Sql.NotLike(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, "%" + (string)cri.Vals[0]));
                        break;
                    case Type.NotLikeMid:
                        sqlList.Add(Sql.NotLike(cri.Pro, pname));
                        paramList.Add(IDbMgr.SetParam(pname, "%" + (string)cri.Vals[0] + "%"));
                        break;
                    default: break;
                }
                if(Type.Or == cri.CriterionType)
                {
                    List<Sql> tmpSqlList1 = new List<Sql>();
                    List<Sql> tmpSqlList2 = new List<Sql>();
                    Fetch(IDbMgr, new Criterion[] {(Criterion)cri.Vals[0]}, tmpSqlList1, paramList, ref uniqueIdCount);
                    Fetch(IDbMgr, new Criterion[] { (Criterion)cri.Vals[1] }, tmpSqlList2, paramList, ref uniqueIdCount);
                    sqlList.Add(Sql.Or(tmpSqlList1[0], tmpSqlList2[0]));
                }
                else if (Type.Or2 == cri.CriterionType)
                {
                    List<Sql> tmpSqlList1 = new List<Sql>();
                    List<Sql> tmpSqlList2 = new List<Sql>();
                    Fetch(IDbMgr, (Criterion[])cri.Vals[0], tmpSqlList1, paramList, ref uniqueIdCount);
                    Fetch(IDbMgr, (Criterion[])cri.Vals[1], tmpSqlList2, paramList, ref uniqueIdCount);
                    sqlList.Add(Sql.Or(tmpSqlList1.ToArray(), tmpSqlList2.ToArray()));
                }
                else if((Type.In == cri.CriterionType || Type.NotIn == cri.CriterionType))
                {
                    if (cri.Vals.Length <= 0)
                        throw new Exception("In ([range]), range is empty.");
                    List<Sql> rangeParams = new List<Sql>();
                    foreach (object o in cri.Vals)
                    {
                        string tmppname = GetUniqueParamName(IDbMgr, ref uniqueIdCount);
                        rangeParams.Add(tmppname);
                        paramList.Add(IDbMgr.SetParam(tmppname, o));
                    }
                    sqlList.Add(Type.In == cri.CriterionType ? (Sql)Sql.InRange(cri.Pro, rangeParams.ToArray())
                        : (Sql)Sql.NotInRange(cri.Pro, rangeParams.ToArray()));
                }
                else if((Type.InDQL == cri.CriterionType || Type.NotInDQL == cri.CriterionType) && cri.Vals.Length == 3)
                {
                    string dqlPro = (string)cri.Vals[0];
                    string dqlTable = (string)cri.Vals[1];
                    Criterion[] dqlCri = (Criterion[])cri.Vals[2];
                    List<Sql> dqlSqlList = new List<Sql>();
                    List<IDbDataParameter> dqlParamList = new List<IDbDataParameter>();
                    Fetch(IDbMgr, dqlCri, dqlSqlList, dqlParamList, ref uniqueIdCount);
                    DMLExp dqlDML = Sql.DML().Select(dqlPro)
                                        .From(dqlTable);
                    if (dqlSqlList.Count > 0)
                        dqlDML.Where(dqlSqlList.ToArray());
                    sqlList.Add(Type.InDQL == cri.CriterionType ? (Sql)Sql.In(dqlDML, cri.Pro) : 
                        (Sql)Sql.NotIn(dqlDML, cri.Pro));
                    paramList.AddRange(dqlParamList);
                }
            }
        }
    }
    #endregion

    #region Entity Manager
    public interface IDBManagerHelper
    {
        string FormatField(string fieldName);
        DMLExp PagingSql(int pageSize, int currentPage, DMLExp sql);
        DbSession CreateSession();
        DbSession OpenSession();
        IDbDataParameter SetParam(string name, object value);
        string MakeParam(string name);
        IDataAdapter CreateAdapter(DbSession ss, DMLExp sql, params IDataParameter[] sqlParams);
    }

    public interface IEntityOperation
    {
        T FindPK<T>(DbSession ss, string table, object[] primaryKeyValues) where T : IDbSessionEntity, new();
        T Find<T>(DbSession ss, string table, params Criterion[] criterions) where T : IDbSessionEntity, new();
        long GetPagingRecords(DbSession ss, string table, params Criterion[] criterions);
        bool Query<T>(DbSession ss, string table, IList<T> list, int curPage, int pageSize, Sql[] orders, params Criterion[] criterions)
            where T : IDbSessionEntity, new();
        bool Query<T>(DbSession ss, string table, IList<T> list, Sql[] orders, params Criterion[] criterions)
            where T : IDbSessionEntity, new();
        bool Insert(DbSession ss, string table, IDbSessionEntity Entity);
        bool Update(DbSession ss, string table, IDbSessionEntity entity);
        bool Delete(DbSession ss, string table, IDbSessionEntity entity);
        bool DeleteEntities(DbSession ss, string table, params Criterion[] cris);
    }

    public abstract class EntityManager
        : IDBManagerHelper, IEntityOperation
    {
        #region IDBManagerHelper Members
        public abstract string FormatField(string fieldName);
        public abstract DMLExp PagingSql(int pageSize, int currentPage, DMLExp sql);
        public abstract DbSession CreateSession();
        public abstract DbSession OpenSession();
        public abstract IDbDataParameter SetParam(string name, object value);
        public abstract string MakeParam(string name);
        public abstract IDataAdapter CreateAdapter(DbSession ss, DMLExp sql, params IDataParameter[] sqlParams);
        #endregion

        #region IEntityOperation Members
        public T FindPK<T>(DbSession ss, string table, object[] primaryKeyValues) where T : IDbSessionEntity, new()
        {
            T entity = new T();
            RowDataWriter rwriter = new RowDataWriter();
            entity.WriteDBData(rwriter);

            List<Sql> queryList = new List<Sql>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();
            int index = 0;
            foreach (PrimaryKey pk in rwriter.PrimaryKeys)
            {
                string paramName = MakeParam(pk.KeyName);
                queryList.Add(Sql.Eq(pk.KeyName, paramName));
                paramList.Add(SetParam(paramName, primaryKeyValues[index++]));
            }

            return ss.GetRowData(entity, Sql.DML().Select("*")
                    .From(table == null ? entity.TableName : table)
                    .Where(queryList.ToArray()), paramList.ToArray()) ? 
                    entity : default(T);
        }

        public T FindPK<T>(DbSession ss, object[] primaryKeyValues) where T : IDbSessionEntity, new()
        {
            return FindPK<T>(ss, null, primaryKeyValues);
        }

        public T Find<T>(DbSession ss, string table, params Criterion[] criterions) where T : IDbSessionEntity, new()
        {
            T entity = new T();
            RowDataWriter rwriter = new RowDataWriter();
            entity.WriteDBData(rwriter);

            List<Sql> queryList = new List<Sql>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();

            int uniqueIdCount = 0;
            Criterion.Fetch(this, criterions, queryList, paramList, ref uniqueIdCount);
            return ss.GetRowData(entity, Sql.DML().Select("*")
                    .From(table == null ? entity.TableName : table)
                    .Where(queryList.ToArray()), paramList.ToArray()) ?
                    entity : default(T);
        }

        public T Find<T>(DbSession ss, params Criterion[] criterions) where T : IDbSessionEntity, new()
        {
            return Find<T>(ss, null, criterions);
        }
        

        public abstract bool Insert(DbSession ss, string table, IDbSessionEntity entity);

        public bool Update(DbSession ss, string table, IDbSessionEntity entity)
        {
            RowDataWriter rwriter = new RowDataWriter();
            entity.WriteDBData(rwriter);

            //get sets and where
            List<EqExp> setList = new List<EqExp>();
            List<Sql> queryList = new List<Sql>();
            List<IDataParameter> dataParams = new List<IDataParameter>();
            foreach (KeyValuePair<string, object> pos in rwriter)
            {
                string pParamName = MakeParam(pos.Key);
                setList.Add(Sql.Eq(pos.Key, pParamName));
                dataParams.Add(SetParam(pParamName, pos.Value));
            }
            foreach(PrimaryKey pk in rwriter.PrimaryKeys)
            {
                string pParamName = MakeParam(pk.KeyName);
                queryList.Add(Sql.Eq(pk.KeyName, pParamName));
                dataParams.Add(SetParam(pParamName, pk.Value));
            }

            //execute
            return ss.Execute(Sql.DML().Update(table == null ? entity.TableName : table)
                            .Set(setList.ToArray())
                            .Where(queryList.ToArray()), dataParams.ToArray());
        }

        public bool Update(DbSession ss, IDbSessionEntity entity)
        {
            return Update(ss, null, entity);
        }

        public bool DeleteEntities(DbSession ss, string table, params Criterion[] cris)
        {
            List<Sql> queryList = new List<Sql>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();

            int unqiueIdCount = 0;
            Criterion.Fetch(this, cris, queryList, paramList, ref unqiueIdCount);

            return ss.Execute(Sql.DML().Delete(table).Where(queryList.ToArray()), paramList.ToArray());
        }

        public bool Delete(DbSession ss, string table, IDbSessionEntity entity)
        {
            RowDataWriter rwriter = new RowDataWriter();
            entity.WriteDBData(rwriter);

            //get query
            List<Sql> queryList = new List<Sql>();
            List<IDataParameter> dataParams = new List<IDataParameter>();
            foreach (PrimaryKey pk in rwriter.PrimaryKeys)
            {
                string pParamName = MakeParam(pk.KeyName);
                queryList.Add(Sql.Eq(pk.KeyName, pParamName));
                dataParams.Add(SetParam(pParamName, pk.Value));
            }

            //execute
            return ss.Execute(Sql.DML().Delete(table == null ? entity.TableName : table).Where(queryList.ToArray()), dataParams.ToArray());
        }

        public bool Delete(DbSession ss, IDbSessionEntity entity)
        {
            return Delete(ss, null, entity);
        }

        public long GetPagingRecords(DbSession ss,  string table,  params Criterion[] criterions)
        {
            List<Sql> sqlList = new List<Sql>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();

            int unqiueIdCount = 0;
            Criterion.Fetch(this, criterions, sqlList, paramList, ref unqiueIdCount);

            DMLExp sql = Sql.DML().Select(Sql.Count("*")).From(table);
            if(criterions != null)
                sql.Where(sqlList.ToArray());
            return ss.GetCellData(sql, paramList.ToArray());
        }

        public bool Query<T>(DbSession ss, string table, IList<T> list, int curPage, int pageSize, Sql[] orders, params Criterion[] criterions) 
            where T : IDbSessionEntity, new()
        {
            List<Sql> sqlList = new List<Sql>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();

            int unqiueIdCount = 0;
            Criterion.Fetch(this, criterions, sqlList, paramList, ref unqiueIdCount);

            DMLExp sql = Sql.DML().Select("*") .From(table == null ? new T().TableName : table);
            if(criterions != null)
                sql.Where(sqlList.ToArray());
            if (orders != null)
                sql.Order(orders);
            if (curPage > 0 && pageSize > 0)
                sql = PagingSql(pageSize, curPage, sql);
            return ss.GetListData<T>(list, sql, paramList.ToArray());
        }

        public bool Query<T>(DbSession ss, IList<T> list, int curPage, int pageSize, Sql[] orders, params Criterion[] criterions) 
            where T : IDbSessionEntity, new()
        {
            return Query<T>(ss, null, list, curPage, pageSize, orders, criterions);
        }

        public bool Query<T>(DbSession ss, string table, IList<T> list, Sql[] orders, params Criterion[] criterions) 
            where T : IDbSessionEntity, new()
        {
            return Query<T>(ss, table, list, 0, 0, orders, criterions);
        }

        public bool Query<T>(DbSession ss, IList<T> list, Sql[] orders, params Criterion[] criterions)
            where T : IDbSessionEntity, new()
        {
            return Query<T>(ss, null, list, 0, 0, orders, criterions);
        }
        #endregion
    }

    public class MSSql2005EntityManager
        : EntityManager
    {
        string m_connectString;
        public MSSql2005EntityManager(string connectString)
        {
            m_connectString = connectString;
        }

        public override string FormatField(string fieldName)
        {
            return "[" + fieldName + "]";
        }

        public override DMLExp PagingSql(int pageSize, int currentPage, DMLExp sql)
        {
            DMLExp sqlPaging = Sql.DML().Select("ROW_NUMBER() over(order by " + sql.GetOrder() + ")" + " as __rowId", sql.GetSelect())
                            .From(sql.GetFrom()).Where(sql.GetWhere());
            long start = (currentPage - 1) * pageSize + 1;
            long end = start + pageSize - 1;
            return Sql.DML().Select(sql.GetSelect())
                            .From("(" + sqlPaging + ") as T")
                            .Where(Sql.Between("__rowId", start.ToString(), end.ToString()));
        }

        public override DbSession CreateSession()
        {
            DbSession ss = DbSession.Create(new System.Data.SqlClient.SqlConnection(m_connectString));
            return ss;
        }

        public override DbSession OpenSession()
        {
            DbSession ss = CreateSession();
            return ss.OpenSession() ? ss : null;
        }

        public override System.Data.IDbDataParameter SetParam(string name, object value)
        {
            if(value == null || value.Equals(DateTime.MinValue))
                return new System.Data.SqlClient.SqlParameter(name, DBNull.Value);
            return new System.Data.SqlClient.SqlParameter(name, value);
        }

        public override string MakeParam(string name)
        {
            return "@" + name;
        }

        public override bool Insert(DbSession ss, string table, IDbSessionEntity Entity)
        {
            RowDataWriter rwriter = new RowDataWriter();
            Entity.WriteDBData(rwriter);

            //set keys & Params
            List<Sql> InsertKeys = new List<Sql>();
            List<Sql> InsertParams = new List<Sql>();
            List<IDbDataParameter> dataParams = new List<IDbDataParameter>();
            string identityCol = "";
            foreach (PrimaryKey pk in rwriter.PrimaryKeys)
            {
                if (pk.PKType == PrimaryKeyType.InCrement
                    || pk.PKType == PrimaryKeyType.Identity)
                {
                    identityCol = pk.KeyName;
                    continue;
                }
                InsertParams.Add(MakeParam(pk.KeyName));
                InsertKeys.Add(pk.KeyName);
                dataParams.Add(SetParam(MakeParam(pk.KeyName), pk.Value));
            }
            foreach (KeyValuePair<string, object> pos in rwriter)
            {
                InsertParams.Add(MakeParam(pos.Key));
                InsertKeys.Add(pos.Key);
                dataParams.Add(SetParam(MakeParam(pos.Key), pos.Value));
            }

            //Execute
            DMLExp insertSql = Sql.DML().Insert(table == null ? Entity.TableName : table, InsertKeys.ToArray())
                                .Values(InsertParams.ToArray());
            if (!string.IsNullOrEmpty(identityCol))
            {
                insertSql.Append(" SELECT SCOPE_IDENTITY() AS SEQUENCE");
                DBObject idd = ss.GetCellData(insertSql, dataParams.ToArray());
                if (idd == null)
                    return false;
                Entity.SetIdentityAfterInsert(idd);
                return true;
            }
            else
                return ss.Execute(insertSql, dataParams.ToArray());
        }


        public bool Insert(DbSession ss, IDbSessionEntity Entity)
        {
            return Insert(ss, null, Entity);
        }

        public override IDataAdapter CreateAdapter(DbSession ss, DMLExp sql, params IDataParameter[] sqlParams)
        {
            System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter(sql, (System.Data.SqlClient.SqlConnection)ss.IConnection);
            ada.SelectCommand.Transaction = (System.Data.SqlClient.SqlTransaction)ss.LevelTran;
            foreach (IDataParameter p in sqlParams)
            {
                ada.SelectCommand.Parameters.Add((System.Data.SqlClient.SqlParameter)p);
            }
            return ada;
        }
    }

    #endregion
}
